
# coding:utf-8
import pandas as pd
import csv
import numpy as np
from datetime import datetime

# 读取文件

dou1 = pd.read_excel(r'E:\每日\周度\10.4\抖音1.xlsx',
                     dtype={'子订单编号': str, '商品ID': str, '达人昵称': str, '选购商品': str})
dou2 = pd.read_excel(r'E:\每日\周度\10.4\抖音2.xlsx',
                     dtype={'子订单编号': str, '商品ID': str, '达人昵称': str, '选购商品': str})
dou3 = pd.read_excel(r'E:\每日\周度\10.4\抖音3.xlsx',
                     dtype={'子订单编号': str, '商品ID': str, '达人昵称': str, '选购商品': str})
dou4 = pd.read_excel(r'E:\每日\周度\10.4\抖音4.xlsx',
                     dtype={'子订单编号': str, '商品ID': str, '达人昵称': str, '选购商品': str})

ld = pd.read_excel(r'E:\每日\周度\10.4\刘栋达人.xlsx', dtype={'抖音号名称': str})

lxx1 = pd.read_excel(r'E:\每日\周度\10.4\罗欣欣达人.xlsx', dtype={'抖音号名称': str})  # (桶装)（袋装）
lxx2 = pd.read_excel(r'E:\每日\周度\10.4\罗欣欣土豆粉达人.xlsx', dtype={'抖音号名称': str})  # (土豆粉)
xhwh1 = pd.read_excel(r'E:\每日\周度\10.4\夕湖文化.xlsx', dtype={'抖音号名称': str})
# hn1 = pd.read_excel(r'E:\每日\周度\10.4\花妞.xlsx', dtype={'抖音号名称': str})  # (土豆粉)
tt1 = pd.read_excel(r'E:\每日\周度\10.4\团团.xlsx', dtype={'抖音号名称': str})  # (土豆粉)
z1 = pd.read_excel(r'E:\每日\周度\10.4\招商1.xlsx', dtype={'订单id': str})
z2 = pd.read_excel(r'E:\每日\周度\10.4\招商2.xlsx', dtype={'订单id': str})
z3 = pd.read_excel(r'E:\每日\周度\10.4\招商3.xlsx', dtype={'订单id': str})
z4 = pd.read_excel(r'E:\每日\周度\10.4\招商4.xlsx', dtype={'订单id': str})

y1 = pd.read_excel(r'E:\每日\周度\10.4\佣金1.xlsx', dtype={'订单id': str})
y2 = pd.read_excel(r'E:\每日\周度\10.4\佣金2.xlsx', dtype={'订单id': str})
y3 = pd.read_excel(r'E:\每日\周度\10.4\佣金3.xlsx', dtype={'订单id': str})
y4 = pd.read_excel(r'E:\每日\周度\10.4\佣金4.xlsx', dtype={'订单id': str})

# 提取对应列
dou1_date = dou1.loc[
    :, ['子订单编号', '商品ID', '选购商品', '商家编码', '商品数量', '订单应付金额', '订单状态', '售后状态', '流量来源',
        '达人昵称', '广告渠道', '流量类型', '流量渠道', '流量体裁', '订单提交时间', '平台实际承担优惠金额',
        '达人实际承担优惠金额']]
dou1_date['平台'] = '抖音1'

dou2_date = dou2.loc[
    :, ['子订单编号', '商品ID', '选购商品', '商家编码', '商品数量', '订单应付金额', '订单状态', '售后状态', '流量来源',
        '达人昵称', '广告渠道', '流量类型', '流量渠道', '流量体裁', '订单提交时间', '平台实际承担优惠金额',
        '达人实际承担优惠金额']]
dou2_date['平台'] = '抖音2'

dou3_date = dou3.loc[
    :, ['子订单编号', '商品ID', '选购商品', '商家编码', '商品数量', '订单应付金额', '订单状态', '售后状态', '流量来源',
        '达人昵称', '广告渠道', '流量类型', '流量渠道', '流量体裁', '订单提交时间', '平台实际承担优惠金额',
        '达人实际承担优惠金额']]
dou3_date['平台'] = '抖音3'

dou4_date = dou4.loc[
    :, ['子订单编号', '商品ID', '选购商品', '商家编码', '商品数量', '订单应付金额', '订单状态', '售后状态', '流量来源',
        '达人昵称', '广告渠道', '流量类型', '流量渠道', '流量体裁', '订单提交时间', '平台实际承担优惠金额',
        '达人实际承担优惠金额']]
dou4_date['平台'] = '抖音4'

dou_date = pd.concat([dou1_date, dou2_date, dou3_date, dou4_date])
dou_date.columns = ['订单id', '商品ID', '选购商品', '商家编码', '商品数量', '订单应付金额', '订单状态', '售后状态',
                    '流量来源', '达人昵称', '广告渠道', '流量类型', '流量渠道', '流量体裁', '订单提交时间',
                    '平台实际承担优惠金额', '达人实际承担优惠金额', '平台']
dou_date['订单提交时间'] = pd.to_datetime(dou_date['订单提交时间'])
dou_date['日期'] = dou_date['订单提交时间'].map(lambda x: datetime.date(x))
date_concat_s = dou_date[(dou_date['订单应付金额'] >= 0.01)]
date_concat_ss = date_concat_s[(date_concat_s['商家编码'] != '抖音直播间赠品')]
# ['商家编码']去空格
date_concat_ss['商品ID'] = date_concat_ss['商品ID'].str.strip()
date_concat_ss['商家编码'] = date_concat_ss['商家编码'].str.strip()
date_concat_ss['订单id'] = date_concat_ss['订单id'].str.strip()

date_concat_ss['成交金额'] = np.where(((date_concat_ss['订单状态'] == "已签收") | (
        date_concat_ss['订单状态'] == "待发货") | (date_concat_ss['订单状态'] == "已完成") | (
                                               date_concat_ss['订单状态'] == "已发货") | (
                                               date_concat_ss['订单状态'] == "已支付") | (
                                               date_concat_ss['订单状态'] == "部分发货"))
                                      & (date_concat_ss['售后状态'] != "退款成功") & (
                                              date_concat_ss['售后状态'] != "售后完成") & (
                                              date_concat_ss['售后状态'] != "退款完成") & (
                                              date_concat_ss['售后状态'] != "已全额退款"),
                                      date_concat_ss['订单应付金额'] + date_concat_ss['平台实际承担优惠金额'] +
                                      date_concat_ss['达人实际承担优惠金额'], 0)

date_concat_ss['成交金额_不含优惠券'] = np.where(((date_concat_ss['订单状态'] == "已签收") | (
        date_concat_ss['订单状态'] == "待发货") | (date_concat_ss['订单状态'] == "已完成") | (
                                                          date_concat_ss['订单状态'] == "已发货") | (
                                                          date_concat_ss['订单状态'] == "已支付") | (
                                                          date_concat_ss['订单状态'] == "部分发货"))
                                                 & (date_concat_ss['售后状态'] != "退款成功") & (
                                                         date_concat_ss['售后状态'] != "售后完成") & (
                                                         date_concat_ss['售后状态'] != "退款完成") & (
                                                         date_concat_ss['售后状态'] != "已全额退款"),
                                                 date_concat_ss['订单应付金额'], 0)

date_concat_ss['成交订单'] = np.where(((date_concat_ss['订单状态'] == "已签收") | (
        date_concat_ss['订单状态'] == "待发货") | (date_concat_ss['订单状态'] == "已完成") | (
                                               date_concat_ss['订单状态'] == "已发货") | (
                                               date_concat_ss['订单状态'] == "已支付") | (
                                               date_concat_ss['订单状态'] == "部分发货"))
                                      & (date_concat_ss['售后状态'] != "退款成功") & (
                                              date_concat_ss['售后状态'] != "售后完成") & (
                                              date_concat_ss['售后状态'] != "退款完成") & (
                                              date_concat_ss['售后状态'] != "已全额退款"), 1, 0)

date_concat_ss['退款金额'] = np.where(
    (date_concat_ss['售后状态'] == '退款成功') | (date_concat_ss['售后状态'] == '退款完成') | (
            date_concat_ss['售后状态'] == '已全额退款') | (date_concat_ss['售后状态'] == '售后完成') | (
            date_concat_ss['售后状态'] == '退货退款完成') | (date_concat_ss['售后状态'] == '交易关闭'),
    date_concat_ss['订单应付金额'] + date_concat_ss['平台实际承担优惠金额'] + date_concat_ss['达人实际承担优惠金额'], 0)

date_concat_ss['退款金额_不含优惠券'] = np.where(
    (date_concat_ss['售后状态'] == '退款成功') | (date_concat_ss['售后状态'] == '退款完成') | (
            date_concat_ss['售后状态'] == '已全额退款') | (date_concat_ss['售后状态'] == '售后完成') | (
            date_concat_ss['售后状态'] == '退货退款完成') | (date_concat_ss['售后状态'] == '交易关闭'),
    date_concat_ss['订单应付金额'], 0)
# 佣金表
yj = pd.concat([y1, y2, y3, y4])
yj_date = yj.loc[:, ['订单id', '作者账号']]
yj_date['订单id'] = yj_date['订单id'].str.strip()
date_concat_sss2 = pd.merge(date_concat_ss, yj_date, on='订单id', how='left')
date_concat_sss2['达人昵称'].fillna(date_concat_sss2['作者账号'], inplace=True)

# 招商表
z1_date = z1.loc[:, ['订单id', '出单机构']]
z2_date = z2.loc[:, ['订单id', '出单机构']]
z3_date = z3.loc[:, ['订单id', '出单机构']]
z4_date = z4.loc[:, ['订单id', '出单机构']]
zs = pd.concat([z1_date, z2_date, z3_date, z4_date])
date_concat_sss = pd.merge(date_concat_sss2, zs, on='订单id', how='left')

# 各投流链接,达人
lc_lj = [
    '3752066905167495351',
    '3754479940293230816'
]  # 良辰链接

lc_dr = []  # 良辰达人

ld_lj = [
    '3524421208374397998',
    '3718871706278953300',
    '3764710268731523333'
]  # 刘栋单独链接

ld_dr = list(ld['抖音号名称'])

lxx1_lj = [
    '3702367630972289253',
    '3753012046921335096',
    '3701090938974765405',
    '3506957255406950391',
    '3520740088860524483',
    '3745946018349449594',
    '3758543932670214558',
    '3758958156538052831',
    '3775109632327024899'
]  # (罗欣欣达人）

lxx1_dr = list(lxx1['抖音号名称'])  # 罗欣欣达人

lxx2_lj = [
    '3748189374425203566',
    '3751738608588030256',
    '3752292420973363354',
    '3737638082678227388',
    '3628547567660489014',
    '3694732441748111734',
    '3754689465759694891',
    '3712805099375034797',
    '3674502993446830541',
    '3766406205405266422'
]  # (土豆粉达人)

lxx2_dr = list(lxx2['抖音号名称'])  # 罗欣欣土豆粉达人
xhwh1_lj = [
    '3751738608588030256',
    '3687141961262825560',
    '3758958156538052831',
    '3723151364247912548'
]  # 夕湖文化

xhwh1_dr = list(xhwh1['抖音号名称'])
# hn1_lj = [
#     # '3701090938974765405',
#     # '3758958156538052831',
#     # '3687141961262825560',
#     # '3775109632327024899'
# ]  # 花妞千川——爆品侠

# hn1_dr = list(hn1['抖音号名称'])

tt1_lj = [
    '3751738608588030256',
    '3758958156538052831',
    '3687141961262825560',
    '3737638082678227388',
    '3628547567660489014',
    '3674502993446830541',
    '3694732441748111734'
]  # 团团别吃了

tt1_dr = list(tt1['抖音号名称'])


def f(x, y, z, v, s, w, p):  # x: 链接 #y: 达人昵称 #z:选购商品 #v:广告渠道 #s:流量来源 #w:出单机构
    if w in ['武汉海大电子商务有限公司']:  # 罗欣欣运营账号
        return '嗨大'
    elif w in ['卓敏科技', '紫金优选', '瀚睿科技', '红利传媒', '来吉团队', '金莎传媒']:  # 罗欣欣运营账号
        return '冷漠'
    elif (x in ld_lj) and ((y in ld_dr) or (pd.isnull(y))):  # 去掉招商达人，加上自然单
        return '刘栋单独链接'
    elif (x in ['3751738608588030256']) and (y in ld_dr):
        return '刘栋'
    elif y in ['harry5']:
        return 'harry5'
    elif y in ['喜喜小助理（直播返场）']:
        return '喜喜投流'
    elif y in ['张喜喜']:
        return '喜喜投流大号'
    elif y in ['美食挖掘机🍜', '饱饱吃不饱', '金金会发光', '味蕾探险家', '饭桶爱说笑']:  # 罗欣欣运营账号
        return '罗欣欣账号'
    elif y in ['啦啦啦lalala', '怪咖美食记', '爱吃的光远', '胖小宛', '食光里的馋猫🐱', '宛禾食品甄选号',
               '大馋丫头可可']:  # 白鹏飞运营账号
        return '白鹏飞账号'
    elif (x in xhwh1_lj) and (y in xhwh1_dr):
        return '夕湖文化'

    elif (x in tt1_lj) and (y in tt1_dr):
        return '罗欣欣'

    elif (x in lxx1_lj) and (y in lxx1_dr):
        return '罗欣欣'

    elif (x in lxx2_lj) and (y in lxx2_dr):
        return '罗欣欣'
    # elif (x in hn1_lj) and (y in hn1_dr):
    #     return '代投花妞'
    # elif (x in ['3751738608588030256']) and (y in hn1_dr):
    #     return '花妞'
    elif (x in lc_lj):
        return '莨辰'
    elif (x in ['3768784577812889610']):
        return '木森'
    elif x in ['3735010733180715297', '3741925082977665083']:  # 罗欣欣单独链接
        return '罗欣欣账号'
    elif x in ['3723120728170692876', '3723119143235485902', '3741926701987398242']:  # 白鹏飞单独链接
        return '白鹏飞账号'

    elif x in ['3714480675638870403', '3741923461510070306', '3550782895822129982', '3526108849595662124',
               '3699396698997653933']:
        return '共用链接自然单'

    elif (v == '商品卡') and (('lcc' in z) or ('lxx' in z)):
        return '罗欣欣3店商品卡'

    elif (('lcc' in z) or ('lxx' in z)):
        return '罗欣欣账号'

    elif y == '宛禾米线官方旗舰店' or ('z1' in z) or (' Z1' in z):
        return '自播1'
    elif y == '宛禾米线速食官方旗舰店' or y == '宛禾米线' or y == '宛禾速食官方旗舰店' or y == '宛禾速食直播间' or (
            'z3' in z) or (' Z3' in z):
        return '自播3'
    elif y == '宛禾米线速食旗舰店' or ('lsf' in z):
        return '螺蛳土豆粉直播间'
    elif y == '宛禾食品旗舰店' or ('z5' in z) or ('Z5' in z):
        return '自播5'
    elif y == '宛禾速食旗舰店' or y == '宛禾食品旗舰店甄选号' or ('z6' in z) or ('Z6' in z):
        return '自播6'
    elif y == '宛禾官方旗舰店' or ('z8' in z) or (' Z8' in z):
        return '自播8'
    elif p == '抖音1' and ('dsp' in z):
        return '自播1'
    elif p == '抖音2' and ('dsp' in z):
        return '自播5'
    elif p == '抖音3' and ('dsp' in z):
        return '自播8'
    elif p == '抖音4' and ('dsp' in z):
        return '螺蛳土豆粉直播间'

    elif (v == '商品卡'):  # 投流商品卡
        return '超级商品卡'

    elif pd.notnull(y):
        return '达人分发'
    elif s == '精选联盟':
        return '达人分发'

    else:
        return '自然单'


date_concat_sss['投流账户'] = list(
    map(f, list(date_concat_sss['商品ID']), list(date_concat_sss['达人昵称']), list(date_concat_sss['选购商品']),
        list(date_concat_sss['广告渠道']), list(date_concat_sss['流量来源']), list(date_concat_sss['出单机构']),
        list(date_concat_sss['平台'])))


def m(x, y, z, v, s, w):  # x: 链接 #y: 达人昵称 #z:选购商品 #v:广告渠道 #s:流量来源 #w:出单机构
    if w in ['武汉海大电子商务有限公司']:
        return '外部团队达人分发'

    elif w in ['卓敏科技', '紫金优选', '瀚睿科技', '红利传媒', '来吉团队', '金莎传媒']:
        return '外部团队达人分发'

    elif (x in ld_lj) and ((y in ld_dr) or (pd.isnull(y))):
        return '内部短视频投流'
    elif (x in ['3751738608588030256']) and (y in ld_dr):
        return '内部短视频投流'

    elif y in ['harry5']:
        return '内部达人分发'

    elif y in ['喜喜小助理（直播返场）']:
        return '内部达人分发'
    elif y in ['张喜喜']:
        return '内部达人分发'
    elif y in ['美食挖掘机🍜', '饱饱吃不饱', '金金会发光', '味蕾探险家', '饭桶爱说笑']:  # 罗欣欣运营账号
        return '内部短视频投流'

    elif y in ['啦啦啦lalala', '怪咖美食记', '爱吃的光远', '胖小宛', '食光里的馋猫🐱', '宛禾食品甄选号',
               '大馋丫头可可']:  # 白鹏飞运营账号
        return '内部短视频投流'

    elif (x in xhwh1_lj) and (y in xhwh1_dr):
        return '外部短视频投流'

    elif (x in tt1_lj) and (y in tt1_dr):
        return '内部达人分发'

    elif (x in lxx1_lj) and (y in lxx1_dr):
        return '内部达人分发'

    elif (x in lxx2_lj) and (y in lxx2_dr):
        return '内部达人分发'
    # elif (x in hn1_lj) and (y in hn1_dr):
    #     return '外部短视频投流'
    # elif (x in ['3751738608588030256']) and (y in hn1_dr):
    #     return '外部短视频投流'
    elif (x in lc_lj):
        return '外部短视频投流'

    elif x in ['3735010733180715297', '3741925082977665083']:  # 罗欣欣单独链接
        return '内部短视频投流'
    elif x in ['3723120728170692876', '3723119143235485902', '3741926701987398242']:  # 白鹏飞单独链接
        return '内部短视频投流'

    elif x in ['3714480675638870403', '3741923461510070306', '3550782895822129982', '3526108849595662124',
               '3699396698997653933']:
        return '内部短视频投流'

    elif (v == '商品卡') and (('lcc' in z) or ('lxx' in z)):
        return '超级商品卡'

    elif ('lcc' in z) or ('lxx' in z):
        return '内部短视频投流'

    elif (x in ['3768784577812889610']):
        return '内部达人分发'

    elif y == '宛禾米线官方旗舰店' or ('z1' in z) or ('Z1' in z):
        return '自播'
    elif y == '宛禾米线速食官方旗舰店' or y == '宛禾米线' or y == '宛禾速食官方旗舰店' or y == '宛禾速食直播间' or y == '宛禾速食' or (
            'z3' in z) or ('Z3' in z):
        return '自播'
    elif y == '宛禾米线速食旗舰店' or ('lsf' in z):
        return '自播'
    elif y == '宛禾食品旗舰店' or ('z5' in z) or ('Z5' in z) or ('dsp' in z):
        return '自播'
    elif y == '宛禾速食旗舰店' or y == '宛禾食品旗舰店甄选号' or ('z6' in z) or ('Z6' in z):
        return '自播'
    elif y == '宛禾官方旗舰店' or ('z8' in z) or ('Z8' in z):
        return '自播'

    elif (v == '商品卡'):  # 投流商品卡
        return '超级商品卡'

    elif pd.notnull(y):
        return '内部达人分发'
    elif s == '精选联盟':
        return '内部达人分发'
    else:
        return '自然单'

    # 应用总渠道分类


date_concat_sss['总渠道'] = list(map(
    m,
    list(date_concat_sss['商品ID']),
    list(date_concat_sss['达人昵称']),
    list(date_concat_sss['选购商品']),
    list(date_concat_sss['广告渠道']),
    list(date_concat_sss['流量来源']),
    list(date_concat_sss['出单机构'])
))

# 按平台和渠道聚合销售额数据（含优惠）
result = date_concat_sss.groupby([
    date_concat_sss['日期'],
    date_concat_sss['平台'],
    date_concat_sss['总渠道'],
    date_concat_sss['投流账户']
], as_index=False).agg(
    销售额_含优惠去退款=('成交金额', 'sum'),
    退款金额=('退款金额', 'sum')
)
result.to_excel(r'E:\每日\周度\10.4结果\投流销售额周度.xlsx',index=False)

# 按平台和渠道聚合销售额数据（不含优惠）
result_bhyh = date_concat_sss.groupby([
    date_concat_sss['日期'],
    date_concat_sss['平台'],
    date_concat_sss['总渠道'],
    date_concat_sss['投流账户']
], as_index=False).agg(
    销售额_不含优惠去退款=('成交金额_不含优惠券', 'sum'),
    成交单量=('成交订单', 'sum'),
    退款金额=('退款金额_不含优惠券', 'sum')
)
result_bhyh.to_excel(r'E:\每日\周度\10.4结果\投流销售额周度_不含优惠.xlsx',index=False)

# SKU级别销售额统计
date_SKU = date_concat_sss
result_sku = date_SKU.groupby([
    date_concat_sss['日期'],
    date_SKU['商品ID'],
    date_SKU['总渠道'],
    date_SKU['投流账户']
], as_index=False).agg(
    销售额_含优惠去退款=('成交金额', 'sum'),
    退款金额=('退款金额', 'sum')
)
result_sku.to_excel(r'E:\每日\周度\10.4结果\投流销售额SKU周度.xlsx',index=False)

# 商家编码级别销售额统计
result_bm = date_SKU.groupby([
    date_concat_sss['日期'],
    date_SKU['商家编码'],
    date_SKU['总渠道'],
    date_SKU['投流账户']
], as_index=False).agg(
    销售额_含优惠去退款=('成交金额', 'sum'),
    退款金额=('退款金额', 'sum')
)
result_bm.to_excel(r'E:\每日\周度\10.4结果\投流销售额编码周度.xlsx',index=False)
